package com.newbingo.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.hibernate.Criteria;
import org.hibernate.Query;
import org.hibernate.SQLQuery;
import org.hibernate.Session;
import org.hibernate.criterion.Expression;
import org.hibernate.criterion.ProjectionList;
import org.hibernate.criterion.Projections;
import org.springframework.orm.hibernate3.support.HibernateDaoSupport;
import com.newbingo.dao.imple.IBettingItemDao;
import com.newbingo.entity.BettingItem;
import com.newbingo.reportEntity.AgentReport;
import com.newbingo.util.Pager;

public class BettingItemDao extends HibernateDaoSupport implements
		IBettingItemDao {
	private static final Log log = LogFactory.getLog(BettingItemDao.class);

	/*
	 * 保存投注项
	 */
	public void save(BettingItem bettingItem) {
		log.debug("Save BettingItem instance");
		try {
			getSession().save(bettingItem);
		} catch (RuntimeException re) {
			log.error("save failed");
			throw re;
		}
	}

	/*
	 * 删除投注项
	 */
	public void delete(BettingItem bettingItem) {
		log.debug("Delete BettingItem instance");
		try {
			BettingItem bi = findById(bettingItem.getItemid());
			getSession().delete(bi);
		} catch (RuntimeException re) {
			log.error("save failed");
			throw re;
		}
	}

	/*
	 * 修改投注项
	 */
	public BettingItem merge(BettingItem bettingItem) {
		log.debug("Delete BettingItem instance");
		try {
			BettingItem result = (BettingItem) getSession().merge(bettingItem);
			return result;
		} catch (RuntimeException re) {
			log.error("save failed");
			throw re;
		}
	}

	/*
	 * 根据投注项ID查找
	 */

	public BettingItem findById(Integer id) {
		log.debug("find BettingItem instance by indentity");
		try {
			BettingItem instance = (BettingItem) getSession().get(
					"com.newbingo.entity.BettingItem", id);
			return instance;
		} catch (RuntimeException re) {
			log.error("save failed");
			throw re;
		}
	}

	/*
	 * 查询所有投注项
	 */
	public List<BettingItem> findAll() {
		log.debug("find all BettingItem instance");
		try {
			String queryString = "from BettingItem";
			Query queryObject = getSession().createQuery(queryString);
			return queryObject.list();
		} catch (RuntimeException re) {
			log.error("find failed");
			throw re;
		}
	}
	
	/*
	 * 根据一期游戏的id查询出所有投注信息
	 */
	public List<BettingItem> findBettingItemsByPeriodGame(Integer pgno){
		log.debug("find BetttingItem instances by PeriodGame");
		try{
			Criteria cri=getSession().createCriteria(BettingItem.class);
			cri.createAlias("periodGame", "pg");
			cri.add(Expression.eq("pg.pgno",pgno));
			cri.setResultTransformer(Criteria.ROOT_ENTITY);
			return cri.list();
		}catch(RuntimeException re){
			log.error("find failed");
			throw re;
		}
	}

	/*
	 * 
	 */
	public List<BettingItem> findByProperty(String propertyName, Object value) {
		log.debug("find all BettingItem instance by property");
		try {
			String queryString = "from BettingItem as model where model."
					+ propertyName + "= ?";
			Query queryObject = getSession().createQuery(queryString);
			queryObject.setParameter(0, value);
			return queryObject.list();
		} catch (RuntimeException re) {
			log.error("find failed");
			throw re;
		}
	}

	/*
	 * 根据是否中奖查询
	 */
	public List<BettingItem> findByWin(boolean win) {
		return findByProperty("win", win);
	}

	/*
	 * 分页条件查询，获取满足条件的记录
	 */
	public Pager findByPage(int start, int limit, Integer pgno, Date startTime,
			Date endTime) {
		System.out.print(start + "\t" + limit);
		Pager page = new Pager();
		Criteria cri = getSession().createCriteria(BettingItem.class);
		cri.createAlias("periodGame", "pg");
		// 添加期数条件
		if (pgno != null) {
			cri.add(Expression.eq("pg.pgno", pgno));
		}
		if (startTime != null && endTime == null)
			cri.add(Expression.ge("betTime", start));
		if (startTime == null && endTime != null)
			cri.add(Expression.le("betTime", endTime));
		if (startTime != null && endTime != null)
			cri.add(Expression.between("betTime", startTime, endTime));
		cri.setProjection(Projections.rowCount());
		page.setTotalSize(((Number) cri.uniqueResult()).intValue());
		cri.setProjection(null);
		cri.setFirstResult(start);
		cri.setMaxResults(limit);
		cri.setResultTransformer(Criteria.ROOT_ENTITY);
		page.setData(cri.list());
		System.out.println(page.getTotalSize() + "\t" + page.getData().size());
		return page;
	}

	/**
	 * 查询会员历史投注记录
	 * 
	 * @param menmberId
	 *            会员编号
	 * @param start
	 * @param limit
	 */
	public Pager findByMemeberId(int start, int limit, int memberId,
			String gameName, Date startTime, Date endTime) {
		Criteria cri = this.getSession().createCriteria(BettingItem.class);
		Pager pager = new Pager();
		cri.createAlias("member", "mb");
		cri.add(Expression.eq("mb.uno", memberId));
		cri.createAlias("bettingCell", "bet");
		cri.createAlias("bet.gamePlay", "gamePlay");
		cri.createAlias("gamePlay.game", "game");
		if (gameName!=null) {
			cri.add(Expression.eq("game.gamename", gameName));
		}
		if (startTime != null && endTime == null) {
			cri.add(Expression.ge("betTime", startTime));
		}
		if (startTime == null && endTime != null) {
			cri.add(Expression.ge("betTime", endTime));
		}
		if (startTime != null && endTime != null) {
			cri.add(Expression.between("betTime", startTime, endTime));
		}
		cri.setProjection(Projections.rowCount());
		pager.setTotalSize(((Number) cri.uniqueResult()).intValue());
		cri.setProjection(null);
		cri.setFirstResult(start);
		cri.setMaxResults(limit);
		cri.setResultTransformer(Criteria.ROOT_ENTITY);

		List<BettingItem> list=cri.list();
		pager.setData(list);
		return pager;
	}
	
	/*
	 * 根据代理商ID和时间段查询每个代理商所有会员投注和返利的总和,生成报表
	 */
	public List findAgentReportByAgent(Integer agentId,String dateStart){
		log.debug("finding the report by agent and the time");
		try {
			Session session=getSession().getSessionFactory().openSession();
			Connection conn=session.connection();
			List<AgentReport> reports=new ArrayList<AgentReport>();
			//定义报表sql语句
//			String sql="SELECT SUM(bt.amount) AS agentSumAmount,SUM(bt.betResult) AS agentSumBetResult," +
//					"SUM(rebatelog.amount) AS agentSumRebate,"+
//					"(SELECT SUM(amount) FROM bettingitem WHERE betTime BETWEEN '"+dateStart+"' AND NOW()) AS sumAmount,"+
//					"(SELECT SUM(betResult) FROM bettingitem WHERE betTime BETWEEN '"+dateStart+"' AND NOW()) AS sumBetResult,"+
//					"(SELECT SUM(amount) FROM gamerebatelog WHERE rebateDate BETWEEN '"+dateStart+"' AND NOW()) AS sumRebate,"+
//					"ag.trueName AS trueName "+
//					"FROM bettingitem bt "+ 
//					"LEFT JOIN member m ON m.uno=bt.member_uno "+ 
//					"LEFT JOIN agent ag ON ag.uno=m.agent_uno "+
//					"LEFT JOIN gamerebatelog rebatelog ON rebatelog.member_uno=m.uno "+
//					"WHERE ag.uno="+agentId+" AND bt.betTime BETWEEN '"+dateStart+"' AND NOW() GROUP BY trueName";
//			PreparedStatement ps=conn.prepareStatement(sql);
//			ResultSet rs=ps.executeQuery();
//			//从ResultSet取出数据，保存到集合
//			while (rs.next()){
//				AgentReport report=new AgentReport();
//				report.setAgentSumAmount(rs.getInt("agentSumAmount"));
//				report.setAgentSumBetResult(rs.getInt("agentSumBetResult"));
//				report.setAgentSumRebate(rs.getInt("agentSumRebate"));
//				report.setSumAmount(rs.getInt("sumAmount"));
//				report.setSumBetResult(rs.getInt("sumBetResult"));
//				report.setSumRebate(rs.getInt("sumRebate"));
//				report.setTrueName(rs.getString("trueName"));
//				reports.add(report);
//			}
//			rs.close();
//			ps.close();
//			conn.close();
			//会员赢相对代理为输,反之亦然
			String sql="SELECT SUM(bt.amount) AS agentSumAmount,SUM(bt.betResult) AS memberSumBetResult,SUM(rebatelog.amount) AS agentSumRebate,SUM(bt.profixAmount) AS agentProfixAmount,"
						+"ag.divinationRate AS divinationRate,ag.profitRate AS profitRate,"
						+"(SELECT SUM(amount) FROM bettingitem WHERE win=1 AND betTime BETWEEN '"+dateStart+"' AND NOW() ) AS lostSumAmount,"
						+"(SELECT SUM(amount) FROM bettingitem WHERE win=0 AND betTime BETWEEN '"+dateStart+"' AND NOW() ) AS winSumAmount,"
						+"ag.trueName AS trueName "
						+"FROM bettingitem bt " 
						+"LEFT JOIN member m ON m.uno=bt.member_uno " 
						+"LEFT JOIN agent ag ON ag.uno=m.agent_uno "
						+"LEFT JOIN gamerebatelog rebatelog ON rebatelog.member_uno=m.uno "
						+"WHERE ag.uno="+agentId+" AND bt.betTime BETWEEN '"+dateStart+"' AND NOW() GROUP BY truename";
			PreparedStatement pstmt=conn.prepareStatement(sql);
			ResultSet rs=pstmt.executeQuery();
			while(rs.next()){
				AgentReport ar=new AgentReport();
				ar.setTrueName(rs.getString("trueName"));
				ar.setSumAmount(rs.getInt("agentSumAmount"));
				ar.setMemberWin(rs.getInt("memberSumBetResult"));
				//查询出代理退水
				ar.setAgentProfit((int)(rs.getInt("agentSumAmount")*rs.getInt("profitRate")/100));
				//计算代理占成
				int agentPercent=(int)((rs.getInt("winSumAmount")-rs.getInt("lostSumAmount"))*rs.getInt("divinationRate")/10);
				ar.setAgentPercent(agentPercent);
				//计算代理结果，为代理的收入
				ar.setAgentResult(ar.getAgentPercent()+ar.getAgentProfit());
				//获取总收入  为投注额-会员输赢
				int income=rs.getInt("agentSumAmount")-rs.getInt("memberSumBetResult");
				ar.setGeneralResult(income-ar.getAgentResult());
				reports.add(ar);
			}
			return reports;
			
		} catch (Exception re) {
			log.error("find failed");
			return null;
		}
	}
	

	
	/*
	 * 查询会员在该时间段内的投注信息，满足条件进行返利
	 */
	public List<BettingItem> findBettingItemsByPeriodTime(Date start,Date end,Integer memberId,Integer gameId){
		log.debug("find bettingitems by memberId and time");
		try{
			Criteria cri=getSession().createCriteria(BettingItem.class);
			cri.createAlias("member", "member");
			cri.createAlias("periodGame", "pg");
			cri.createAlias("pg.gameTable", "gt");
			cri.createAlias("gt.game", "game");
			cri.add(Expression.eq("member.uno", memberId));
			cri.add(Expression.eq("game.gameno", gameId));
			cri.add(Expression.between("betTime", start,end));
			cri.setResultTransformer(Criteria.ROOT_ENTITY);
			return (List<BettingItem>)cri.list();
		}catch(RuntimeException re){
			log.error("find failed");
			throw re;
		}
	}
	
	
}
